package com.hwg.emp.dao.impl;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.List;

import com.hwg.emp.common.dto.KeyQueryDto;
import com.hwg.emp.common.util.DBUtil;
import com.hwg.emp.dao.DeptDAO;
import com.hwg.emp.model.DeptMD;
import com.hwg.emp.model.EmployeeMD;
import com.hwg.emp.model.UserMD;

public class DeptDAOImpl implements DeptDAO {

	@Override
	public int insert(DeptMD t) throws Exception {
		Connection con=DBUtil.getConnection();
		StringBuilder sql=new StringBuilder();
		sql.append("insert into emp_dept(id,name,manager_id,remark)")
		.append(" values")
		.append("(?,?,?,?)");
		PreparedStatement ps=con.prepareStatement(sql.toString());
		//定义问号索引
		int i=0;
		ps.setString(++i, t.getId());
		ps.setString(++i, t.getName());
		ps.setString(++i, t.getManager().getId());
		ps.setString(++i, t.getRemark());
		return ps.executeUpdate();
	}

	@Override
	public int delete(String uk, String ukValue) throws Exception {
		Connection con=DBUtil.getConnection();
		StringBuilder sql=new StringBuilder();
		sql.append("delete  from emp_dept where ")
		.append(uk).append("=?");
		PreparedStatement ps=con.prepareStatement(sql.toString());
		ps.setObject(1, ukValue);
		return ps.executeUpdate();
	}

	@Override
	public int update(DeptMD t) throws Exception {
		Connection con=DBUtil.getConnection();
		StringBuilder sql=new StringBuilder();
		sql.append("update emp_dept set name=?,manager_id=?,members=?,remark=?")
		.append(" where ")
		.append("id=?");
		PreparedStatement ps=con.prepareStatement(sql.toString());
		//定义问号索引
		int i=1;
		ps.setString(i++, t.getName());
		ps.setString(i++, t.getManager().getId());
		ps.setInt(i++, t.getMembers());
		ps.setString(i++, t.getRemark());
		ps.setString(i++, t.getId());
		return ps.executeUpdate();
	}

	@Override
	public DeptMD select(String uk, String ukValue) throws Exception {
		Connection con=DBUtil.getConnection();
		StringBuilder sql=new StringBuilder();
		sql.append("select * from emp_dept where ")
		.append(uk).append("=?"  );
		PreparedStatement ps=con.prepareStatement(sql.toString());
		ps.setObject(1, ukValue);
		ResultSet rs=ps.executeQuery();
		if(rs.next()){
			return result2dm(rs);
		}
		return null;
	}
	
	/**
	 * 拼凑结果集
	 * @param rs 传入一个结果集
	 * @return 用户对象
	 * @throws SQLException
	 */
	private DeptMD result2dm(ResultSet rs)throws SQLException{
		DeptMD dm=new DeptMD();
		dm.setId(rs.getString("id"));
		dm.setMembers(rs.getInt("members"));
		dm.setName(rs.getString("name"));
		dm.setRemark(rs.getString("remark"));
		UserMD manager = new UserMD();
		manager.setId(rs.getString("manager_id"));
		dm.setManager(manager);
		return dm;
	}
	
	@Override
	public boolean exist(String uk, String ukValue) throws Exception {
		Connection con=DBUtil.getConnection();
		StringBuilder sql=new StringBuilder();
		sql.append("select count(*) from emp_dept where ")
		.append(uk).append("=?");
		PreparedStatement ps=con.prepareStatement(sql.toString());
		ps.setObject(1, ukValue);
		ResultSet rs=ps.executeQuery();
		if(rs.next()){
			return rs.getInt(1)>0;
		}
		return false;
	}

	@Override
	public int selectByKey(KeyQueryDto dto, List<DeptMD> pageData) throws Exception {
		//获取查询条件
		String key=dto.getKey();
		//1查总记录
		Connection connection=DBUtil.getConnection();
		//组装wheresql
		StringBuilder whereSQL=new StringBuilder();
		whereSQL.append(" from emp_dept ");
		if(key!=null){
			key="%"+key+"%";
			whereSQL.append(" where name like ?");
		}
		
		PreparedStatement ps=connection.prepareStatement("select count(*) "+whereSQL);
		if(key!=null){
			ps.setString(1, key);
		}
		ResultSet rs=ps.executeQuery();
		int total=0;
		if(rs.next()){
			total=rs.getInt(1);
		}
		rs.close();
		ps.close();
		if(total==0){
			return 0;
		}
		//2分页查询
		int pageNo=dto.getPageNo();
		int pageSize=dto.getPageSize();
		StringBuilder pageSql=new StringBuilder();
		pageSql.append("select * ").append(whereSQL)
		.append(" order by name desc") 
		.append(" limit ").append((pageNo-1)*pageSize)
		.append(",").append(pageSize);
		ps=connection.prepareStatement(pageSql.toString());
		if(key!=null){
			ps.setString(1, key);
		}
		rs=ps.executeQuery();
		while(rs.next()){
			pageData.add(result2dm(rs));
		}
		rs.close();
		ps.close();
		return total;
	}

	@Override
	public DeptMD selectOneDept(String name) throws Exception {
		DeptMD dept = new DeptMD();
		String sql = "SELECT * FROM emp_dept WHERE NAME=?";
		Connection con = DBUtil.getConnection();
		PreparedStatement ps = con.prepareStatement(sql);
		ps.setString(1, name);
		ResultSet rs = ps.executeQuery();
		while (rs.next()) {
			dept.setId(rs.getString("id"));
			dept.setMembers(rs.getInt("members"));
			
		}
		return dept;
	}

	@Override
	public int updateName(DeptMD dept) throws Exception {
		Connection con=DBUtil.getConnection();
		StringBuilder sql=new StringBuilder();
		sql.append("update emp_dept set name=?")
		.append(" where ")
		.append("id=?");
		PreparedStatement ps=con.prepareStatement(sql.toString());
		//定义问号索引
		int i=0;
		ps.setString(++i, dept.getName());
		ps.setString(++i, dept.getId());
		return ps.executeUpdate();
	}

	@Override
	public int updateMembersPlusOne(DeptMD dept) throws Exception {
		Connection con=DBUtil.getConnection();
		StringBuilder sql=new StringBuilder();
		sql.append("update emp_dept set members=?")
		.append(" where ")
		.append("id=?");
		PreparedStatement ps=con.prepareStatement(sql.toString());
		//定义问号索引
		int i=0;
		ps.setInt(++i, dept.getMembers()+1);
		ps.setString(++i, dept.getId());
		return ps.executeUpdate();
	}

	@Override
	public int updateMembersMinusOne(DeptMD dept) throws Exception {
		Connection con=DBUtil.getConnection();
		StringBuilder sql=new StringBuilder();
		sql.append("update emp_dept set members=?")
		.append(" where ")
		.append("id=?");
		PreparedStatement ps=con.prepareStatement(sql.toString());
		//定义问号索引
		int i=0;
		ps.setInt(++i, dept.getMembers()-1);
		ps.setString(++i, dept.getId());
		return ps.executeUpdate();
	}

}
